package com.yangwudongxi.sqlloghelper.service;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.yangwudongxi.sqlloghelper.dto.ExecutionPlanStep;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.*;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestTemplate;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * DeepSeek AI分析服务
 *
 * @author 氧屋东西
 */
@Slf4j
@Service
public class DeepSeekService {
    
    @Value("${deepseek.api-key}")
    private String apiKey;
    
    @Value("${deepseek.api-url}")
    private String apiUrl;
    
    @Value("${deepseek.model}")
    private String model;
    
    @Value("${deepseek.timeout:30000}")
    private Integer timeout;
    
    private final RestTemplate restTemplate;
    private final ObjectMapper objectMapper;
    
    public DeepSeekService() {
        this.restTemplate = new RestTemplate();
        this.objectMapper = new ObjectMapper();
    }
    
    /**
     * 分析SQL执行计划（带建表语句）
     */
    public String analyzeExecutionPlan(String sql, List<ExecutionPlanStep> executionPlan, Map<String, String> tableCreateStatements) {
        try {
            String prompt = buildAnalysisPrompt(sql, executionPlan, tableCreateStatements);
            log.info("调用DeepSeek API分析执行计划，SQL: {}", sql.substring(0, Math.min(sql.length(), 100)) + "...");
            
            Map<String, Object> requestBody = buildRequestBody(prompt);
            HttpHeaders headers = buildHeaders();
            HttpEntity<Map<String, Object>> entity = new HttpEntity<>(requestBody, headers);
            
            ResponseEntity<Map> response = restTemplate.postForEntity(apiUrl, entity, Map.class);
            
            if (response.getStatusCode() == HttpStatus.OK && response.getBody() != null) {
                return extractAnalysisFromResponse(response.getBody());
            } else {
                log.error("DeepSeek API调用失败，状态码: {}", response.getStatusCode());
                return "AI分析服务暂时不可用，请稍后重试。";
            }
            
        } catch (Exception e) {
            log.error("DeepSeek API调用异常", e);
            return "AI分析过程中出现错误：" + e.getMessage();
        }
    }
    
    /**
     * 构建分析提示词（增强版，包含建表语句）
     */
    private String buildAnalysisPrompt(String sql, List<ExecutionPlanStep> executionPlan, Map<String, String> tableCreateStatements) {
        StringBuilder prompt = new StringBuilder();
        prompt.append("你是一个mysql数据库专家，请分析以下MySQL查询的执行计划，并结合表结构提供全面的性能优化建议：\n\n");
        
        prompt.append("SQL语句：\n");
        prompt.append(sql).append("\n\n");
        
        // 添加涉及的表结构信息
        if (tableCreateStatements != null && !tableCreateStatements.isEmpty()) {
            prompt.append("涉及的表结构：\n");
            for (Map.Entry<String, String> entry : tableCreateStatements.entrySet()) {
                prompt.append("表名: ").append(entry.getKey()).append("\n");
                prompt.append(entry.getValue()).append("\n\n");
            }
        }
        
        prompt.append("执行计划：\n");
        for (int i = 0; i < executionPlan.size(); i++) {
            ExecutionPlanStep step = executionPlan.get(i);
            prompt.append(String.format("步骤%d: ", i + 1));
            prompt.append(String.format("表=%s, 类型=%s, ", 
                    step.getTable(), step.getType()));
            prompt.append(String.format("可能索引=%s, 使用索引=%s, ", 
                    step.getPossibleKeys(), step.getKey()));
            prompt.append(String.format("预估行数=%s, 额外信息=%s\n", 
                    step.getRows(), step.getExtra()));
        }
        
        prompt.append("\n请从以下几个方面进行深度分析：\n");
        prompt.append("1. SQL功能分析：基于表结构分析此查询实现的业务功能和查询目的\n");
        prompt.append("2. 执行计划解析：详细解释每个步骤及其性能含义\n");
        prompt.append("3. 表结构评估：分析表设计是否合理，字段类型、长度是否恰当\n");
        prompt.append("4. 索引优化策略：结合表结构和查询模式，推荐具体的索引创建方案（包含完整的CREATE INDEX语句）\n");
        prompt.append("5. JOIN优化建议：基于表关系和数据分布优化连接策略\n");
        prompt.append("6. SQL重写方案：提供具体的优化SQL语句，解释改写原因\n");
        prompt.append("7. 性能风险预警：指出潜在的性能风险点和扩展性问题\n");
        prompt.append("8. 数据类型优化：检查字段类型使用是否合理，是否有优化空间\n");
        prompt.append("9. 整体优化建议：给出综合的优化方向和预期性能提升效果\n\n");
        prompt.append("请用中文回答，条理清晰，提供具体可执行的优化方案。对于索引建议，请给出完整的CREATE INDEX语句。");
        
        return prompt.toString();
    }
    
    /**
     * 构建请求体
     */
    private Map<String, Object> buildRequestBody(String prompt) {
        Map<String, Object> requestBody = new HashMap<>();
        requestBody.put("model", model);
        requestBody.put("max_tokens", 3000);
        requestBody.put("temperature", 0.1);
        
        Map<String, Object> message = new HashMap<>();
        message.put("role", "user");
        message.put("content", prompt);
        
        requestBody.put("messages", Arrays.asList(message));
        
        return requestBody;
    }
    
    /**
     * 构建请求头
     */
    private HttpHeaders buildHeaders() {
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);
        headers.set("Authorization", "Bearer " + apiKey);
        return headers;
    }
    
    /**
     * 生成ER图分析
     */
    public com.yangwudongxi.sqlloghelper.dto.ErDiagramResult generateErDiagram(
            com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.ErData erData,
            com.yangwudongxi.sqlloghelper.dto.DatabaseConfig dbConfig) {
        try {
            String prompt = buildErDiagramPrompt(erData);
            log.info("调用DeepSeek API生成ER图，包含{}个SQL语句，{}个查询结果", 
                    erData.getSqlStatements().size(), erData.getQueryResults().size());
            
            Map<String, Object> requestBody = buildRequestBody(prompt);
            HttpHeaders headers = buildHeaders();
            HttpEntity<Map<String, Object>> entity = new HttpEntity<>(requestBody, headers);
            
            ResponseEntity<Map> response = restTemplate.postForEntity(apiUrl, entity, Map.class);
            
            if (response.getStatusCode() == HttpStatus.OK && response.getBody() != null) {
                String aiAnalysis = extractAnalysisFromResponse(response.getBody());
                return parseErDiagramFromAnalysis(aiAnalysis, erData);
            } else {
                log.error("DeepSeek API调用失败，状态码: {}", response.getStatusCode());
                throw new RuntimeException("AI分析服务暂时不可用，请稍后重试。");
            }
            
        } catch (Exception e) {
            log.error("DeepSeek API调用异常", e);
            throw new RuntimeException("AI分析过程中出现错误：" + e.getMessage());
        }
    }
    
    /**
     * 构建ER图分析提示词
     */
    private String buildErDiagramPrompt(com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.ErData erData) {
        StringBuilder prompt = new StringBuilder();
        prompt.append("你是一个mysql数据库专家，请分析以下SQL语句和查询结果，生成数据库表的ER关系图。\n\n");
        
        // SQL语句部分
        prompt.append("SQL语句信息：\n");
        for (int i = 0; i < erData.getSqlStatements().size(); i++) {
            com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.SqlStatement stmt = erData.getSqlStatements().get(i);
            prompt.append(String.format("SQL%d [%s]：%s\n", i + 1, stmt.getType(), stmt.getSql()));
            if (stmt.getTables() != null && !stmt.getTables().isEmpty()) {
                prompt.append("涉及表：").append(String.join(", ", stmt.getTables())).append("\n");
            }
            prompt.append("\n");
        }
        
        // 查询结果部分
        prompt.append("查询结果数据（仅展示前10行）：\n");
        for (int i = 0; i < erData.getQueryResults().size(); i++) {
            com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.QueryResult result = erData.getQueryResults().get(i);
            prompt.append(String.format("结果%d [%s]：\n", i + 1, result.getType()));
            
            if (result.getResult() != null && result.getResult().getColumns() != null) {
                // 列信息
                prompt.append("列信息：");
                for (com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.ColumnInfo col : result.getResult().getColumns()) {
                    prompt.append(String.format(" %s(%s)", col.getName(), col.getDataType()));
                    if (col.getComment() != null && !col.getComment().isEmpty()) {
                        prompt.append("[").append(col.getComment()).append("]");
                    }
                }
                prompt.append("\n");
                
                // 数据行（限制前10行）
                if (result.getResult().getRows() != null && !result.getResult().getRows().isEmpty()) {
                    int rowLimit = Math.min(10, result.getResult().getRows().size());
                    for (int j = 0; j < rowLimit; j++) {
                        Map<String, Object> row = result.getResult().getRows().get(j);
                        prompt.append("行").append(j + 1).append("：");
                        for (com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.ColumnInfo col : result.getResult().getColumns()) {
                            Object value = row.get(col.getName());
                            prompt.append(col.getName()).append("=").append(value).append(" ");
                        }
                        prompt.append("\n");
                    }
                }
            }
            prompt.append("\n");
        }
        
        // 表信息部分
        if (erData.getTableInfo() != null && !erData.getTableInfo().isEmpty()) {
            prompt.append("表统计信息：\n");
            for (Map.Entry<String, com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.TableInfo> entry : erData.getTableInfo().entrySet()) {
                com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.TableInfo tableInfo = entry.getValue();
                prompt.append(String.format("表%s：记录数=%d", entry.getKey(), tableInfo.getCount()));
                if (tableInfo.getComment() != null && !tableInfo.getComment().isEmpty()) {
                    prompt.append("，注释=").append(tableInfo.getComment());
                }
                prompt.append("\n");
            }
        }
        
        prompt.append("\n基于以上信息，请分析数据库表之间的关系，并按以下JSON格式输出ER图结构：\n");
        prompt.append("{\n");
        prompt.append("  \"tables\": [\n");
        prompt.append("    {\n");
        prompt.append("      \"name\": \"表名\",\n");
        prompt.append("      \"comment\": \"表注释\",\n");
        prompt.append("      \"fields\": [\n");
        prompt.append("        {\n");
        prompt.append("          \"name\": \"字段名\",\n");
        prompt.append("          \"type\": \"字段类型\",\n");
        prompt.append("          \"comment\": \"字段注释\",\n");
        prompt.append("          \"isPrimaryKey\": true/false,\n");
        prompt.append("          \"isForeignKey\": true/false\n");
        prompt.append("        }\n");
        prompt.append("      ]\n");
        prompt.append("    }\n");
        prompt.append("  ],\n");
        prompt.append("  \"relationships\": [\n");
        prompt.append("    {\n");
        prompt.append("      \"fromTable\": \"源表\",\n");
        prompt.append("      \"toTable\": \"目标表\",\n");
        prompt.append("      \"type\": \"one-to-one|one-to-many|many-to-many\",\n");
        prompt.append("      \"label\": \"关系描述\",\n");
        prompt.append("      \"fromField\": \"源字段\",\n");
        prompt.append("      \"toField\": \"目标字段\"\n");
        prompt.append("    }\n");
        prompt.append("  ]\n");
        prompt.append("}\n\n");
        prompt.append("请分析表之间的主外键关系、业务关联关系，推断出合理的ER图结构。");
        prompt.append("要求：1.分析准确，关系明确 2.JSON格式正确 3.用中文注释说明关系含义");
        
        return prompt.toString();
    }
    
    /**
     * 解析AI分析结果为ER图结构
     */
    private com.yangwudongxi.sqlloghelper.dto.ErDiagramResult parseErDiagramFromAnalysis(
            String aiAnalysis, com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.ErData erData) {
        try {
            // 尝试从AI分析结果中提取JSON部分
            String jsonStr = extractJsonFromAnalysis(aiAnalysis);
            if (jsonStr != null) {
                Map<String, Object> jsonResult = objectMapper.readValue(jsonStr, Map.class);
                return buildErDiagramResult(jsonResult, aiAnalysis);
            }
            
            // 如果无法解析JSON，返回基础结构
            log.warn("无法从AI分析中提取JSON，返回基础表结构");
            return buildBasicErDiagramResult(erData, aiAnalysis);
            
        } catch (Exception e) {
            log.error("解析ER图分析结果失败", e);
            return buildBasicErDiagramResult(erData, aiAnalysis);
        }
    }
    
    /**
     * 从分析结果中提取JSON字符串
     */
    private String extractJsonFromAnalysis(String analysis) {
        try {
            // 查找JSON开始和结束位置
            int startIndex = analysis.indexOf("{");
            int endIndex = analysis.lastIndexOf("}");
            
            if (startIndex != -1 && endIndex != -1 && endIndex > startIndex) {
                return analysis.substring(startIndex, endIndex + 1);
            }
            
            return null;
        } catch (Exception e) {
            log.warn("提取JSON字符串失败", e);
            return null;
        }
    }
    
    /**
     * 构建ER图结果
     */
    @SuppressWarnings("unchecked")
    private com.yangwudongxi.sqlloghelper.dto.ErDiagramResult buildErDiagramResult(
            Map<String, Object> jsonResult, String aiAnalysis) {
        java.util.List<com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.TableInfo> tables = new java.util.ArrayList<>();
        java.util.List<com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.Relationship> relationships = new java.util.ArrayList<>();
        
        // 解析表信息
        List<Map<String, Object>> tablesJson = (List<Map<String, Object>>) jsonResult.get("tables");
        if (tablesJson != null) {
            for (Map<String, Object> tableJson : tablesJson) {
                java.util.List<com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.FieldInfo> fields = new java.util.ArrayList<>();
                
                List<Map<String, Object>> fieldsJson = (List<Map<String, Object>>) tableJson.get("fields");
                if (fieldsJson != null) {
                    for (Map<String, Object> fieldJson : fieldsJson) {
                        com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.FieldInfo field = 
                                com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.FieldInfo.builder()
                                        .name((String) fieldJson.get("name"))
                                        .type((String) fieldJson.get("type"))
                                        .comment((String) fieldJson.get("comment"))
                                        .isPrimaryKey((Boolean) fieldJson.get("isPrimaryKey"))
                                        .isForeignKey((Boolean) fieldJson.get("isForeignKey"))
                                        .build();
                        fields.add(field);
                    }
                }
                
                com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.TableInfo table = 
                        com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.TableInfo.builder()
                                .name((String) tableJson.get("name"))
                                .comment((String) tableJson.get("comment"))
                                .fields(fields)
                                .build();
                tables.add(table);
            }
        }
        
        // 解析关系信息
        List<Map<String, Object>> relationshipsJson = (List<Map<String, Object>>) jsonResult.get("relationships");
        if (relationshipsJson != null) {
            for (Map<String, Object> relJson : relationshipsJson) {
                com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.Relationship relationship = 
                        com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.Relationship.builder()
                                .fromTable((String) relJson.get("fromTable"))
                                .toTable((String) relJson.get("toTable"))
                                .type((String) relJson.get("type"))
                                .label((String) relJson.get("label"))
                                .fromField((String) relJson.get("fromField"))
                                .toField((String) relJson.get("toField"))
                                .build();
                relationships.add(relationship);
            }
        }
        
        return com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.builder()
                .tables(tables)
                .relationships(relationships)
                .aiAnalysis(aiAnalysis)
                .build();
    }
    
    /**
     * 构建基础ER图结果（当无法解析AI结果时）
     */
    private com.yangwudongxi.sqlloghelper.dto.ErDiagramResult buildBasicErDiagramResult(
            com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.ErData erData, String aiAnalysis) {
        java.util.List<com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.TableInfo> tables = new java.util.ArrayList<>();
        java.util.Set<String> tableNames = new java.util.HashSet<>();
        
        // 从SQL语句中收集表名
        for (com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.SqlStatement stmt : erData.getSqlStatements()) {
            if (stmt.getTables() != null) {
                tableNames.addAll(stmt.getTables());
            }
        }
        
        // 构建基础表信息
        for (String tableName : tableNames) {
            java.util.List<com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.FieldInfo> fields = new java.util.ArrayList<>();
            
            // 尝试从查询结果中推断字段信息
            for (com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.QueryResult result : erData.getQueryResults()) {
                if (result.getResult() != null && result.getResult().getColumns() != null) {
                    for (com.yangwudongxi.sqlloghelper.dto.ErDiagramRequest.ColumnInfo col : result.getResult().getColumns()) {
                        com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.FieldInfo field = 
                                com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.FieldInfo.builder()
                                        .name(col.getName())
                                        .type(col.getDataType())
                                        .comment(col.getComment())
                                        .isPrimaryKey(false)
                                        .isForeignKey(false)
                                        .build();
                        fields.add(field);
                    }
                    break; // 只取第一个结果的字段信息
                }
            }
            
            String tableComment = "";
            if (erData.getTableInfo() != null && erData.getTableInfo().get(tableName) != null) {
                tableComment = erData.getTableInfo().get(tableName).getComment();
            }
            
            com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.TableInfo table = 
                    com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.TableInfo.builder()
                            .name(tableName)
                            .comment(tableComment)
                            .fields(fields)
                            .build();
            tables.add(table);
        }
        
        return com.yangwudongxi.sqlloghelper.dto.ErDiagramResult.builder()
                .tables(tables)
                .relationships(new java.util.ArrayList<>())
                .aiAnalysis(aiAnalysis)
                .build();
    }
    
    /**
     * 从响应中提取分析结果
     */
    @SuppressWarnings("unchecked")
    private String extractAnalysisFromResponse(Map<String, Object> responseBody) {
        try {
            List<Map<String, Object>> choices = (List<Map<String, Object>>) responseBody.get("choices");
            if (choices != null && !choices.isEmpty()) {
                Map<String, Object> firstChoice = choices.get(0);
                Map<String, Object> message = (Map<String, Object>) firstChoice.get("message");
                if (message != null) {
                    String content = (String) message.get("content");
                    return content != null ? content.trim() : "AI分析结果为空";
                }
            }
            
            log.warn("DeepSeek API响应格式异常: {}", responseBody);
            return "AI分析响应格式异常，请稍后重试。";
            
        } catch (Exception e) {
            log.error("解析DeepSeek API响应失败", e);
            return "AI分析结果解析失败：" + e.getMessage();
        }
    }
}